-- exec sp_load_transit_lots_for_ftp '0', 'FAB,Sparse Probe,SORT,Die Bank', '0','UTAC','UPLOADCSV', 'FALSE'
ALTER PROCEDURE sp_load_transit_lots_for_ftp (

@PROCESS_STEP_IDS varchar(2000),
@PROCESS_TYPE VARCHAR(100),
@VENDOR_NAME	VARCHAR(50),
@NEXT_LOCATION  VARCHAR(50),
@REPORT_TYPE    VARCHAR(50),
@BY_JITWINDOW	VARCHAR(10)
)

AS
begin
	
	declare @ship_jit_window int
	DECLARE @PROCESSTEP_ID INT
	DECLARE @STD_COST NUMERIC(18,4)
	DECLARE @GDPW INT
	DECLARE @SHIPPEDLOT_ID INT
	DECLARE @WAFER_ID_TABLE	 TABLE ( WAFER_ID INT)
	DECLARE @WAFER_IDS_AS_COMMA VARCHAR(100)
	DECLARE @WAFER_IDS VARCHAR(100)
	DECLARE @WAFER_ID INT
	DECLARE @PS_TYPE VARCHAR(50)
	DECLARE @QUARTER_NO		INT
	DECLARE @YEAR			INT
	DECLARE @LOT_NUMBER VARCHAR(50)

	DECLARE @TRANSITCURSOR CURSOR 

	CREATE TABLE #RESULTS ([Lot Number] varchar(50), [Fab Lot Number] varchar(50), [Part Number] varchar(50), [PO Number] varchar(50),[SO Number] varchar(50),[Invoice No] varchar(50),[Process] varchar(50),
				[From Location] varchar(50), [Ship To] varchar(100), [Ship Date] datetime, [Ship Qty] int, Ps_type_Id int, WAFER_ID VARCHAR(100), ShippedLot_Id int, ProcessStep_Id INT, GDPW INT, STD_COST NUMERIC(18,4) )

    CREATE TABLE #RESULTS_BYWAFER ([Invoice No] varchar(50), [PO] VARCHAR(50), [Part No] varchar(50),[Wafer lot no.] varchar(50), [Wafer ID] INT , [Wafer Qty] INT, 
									[Die Value] NUMERIC(18,4), [Ship TO] varchar(100), [Packing#] VARCHAR(50),[Vendor] varchar(50) )

	SELECT @YEAR = CC.CC_YEAR, @QUARTER_NO = CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR CQC, COMPANY_CALENDAR CC
		WHERE CQC.CQC_YEARID = CC.CC_YEARID 
		AND CURRENT_TIMESTAMP >= CQC_START_DATE 
		AND CURRENT_TIMESTAMP <= CQC_END_DATE
	
	DECLARE @QUERYSTRING	VARCHAR(4000)

	SELECT @ship_jit_window = Ship_JIT_WINDOW from Client_Table 
	
	IF @ship_jit_window IS NULL
	BEGIN
			SET @ship_jit_window = 0
	END
	SET @QUERYSTRING = N'INSERT INTO #RESULTS select LT.Lot_Number , LT.Fab_Lot_Number, P.Part_Number , ISNULL(SL.SL_Customer_PO,LT.PO_NUMBER) , SL.SL_Customer_SO,
		SL.SL_Invoice_Number , PS.PS_Type, (SELECT L_VENDOR_NAME FROM lOCATION where location_id = LT.Location_ID ) , SL.SL_NextLocation, SL.Ship_Date, 
		SL.Ship_Qty, (SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = PS.PS_Type),SL.WAFER_ID, SL.ShippedLot_Id, PS.ProcessStep_ID, NULL, NULL
		from Lot_Table LT, Shipped_Lot SL, Product P, Process_Step PS
		where SL.Lot_ID = LT.Lot_ID and LT.ProcessStep_ID = P.ProcessStep_ID and P.ProcessStep_ID = PS.ProcessStep_ID
		and SL.Status = ''TRANSIT'' and SL.Ship_Status <= 0 '
	
	IF @BY_JITWINDOW IS NOT NULL AND @BY_JITWINDOW = 'TRUE'
	BEGIN
		SET @QUERYSTRING = @QUERYSTRING + '  and DATEDIFF(day, SL.Ship_Date, getdate()) >= ' + CONVERT(VARCHAR,@ship_jit_window)
	END

	IF @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0'
 	BEGIN
 		SET @QUERYSTRING = @QUERYSTRING + '  AND PS_TYPE IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ( ''' + @PROCESS_TYPE + ''') ) '
	END
	
 	IF ( @PROCESS_STEP_IDS IS NOT NULL AND @PROCESS_STEP_IDS <> '0'  )
 	BEGIN
		SET @QUERYSTRING = @QUERYSTRING +' AND P.PROCESSSTEP_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PROCESS_STEP_IDS +''') )'
	END
	IF ( @VENDOR_NAME IS NOT NULL AND @VENDOR_NAME <> '0'  )
	BEGIN
 		SET @QUERYSTRING = @QUERYSTRING +' AND LT.LOCATION_ID IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME= ''' +@VENDOR_NAME + ''') '
 	END
	IF ( @NEXT_LOCATION IS NOT NULL AND @NEXT_LOCATION <> '0'  )
	BEGIN
 		SET @QUERYSTRING = @QUERYSTRING +' AND SL.SL_NextLocation = ''' +@NEXT_LOCATION + ''' '
 	END
	
	EXEC (@QUERYSTRING)

	SET @TRANSITCURSOR = CURSOR FOR SELECT ProcessStep_Id, [Process], ShippedLot_Id,WAFER_ID,[Lot Number]  FROM #RESULTS
	OPEN @TRANSITCURSOR
	FETCH NEXT FROM @TRANSITCURSOR INTO @PROCESSTEP_ID,@PS_TYPE, @SHIPPEDLOT_ID, @WAFER_IDS, @LOT_NUMBER
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--ELSE IF @PS_TYPE = 'SORT'
		--BEGIN
			--SELECT @GDPW = BIN_1 FROM INFORMATIVE_BINNING WHERE LOT_ID = (SELECT LOT_ID FROM Shipped_Lot Where ShippedLot_Id = @SHIPPEDLOT_ID ) AND Wafer_ID = @WAFER_IDS
			--	Select @GDPW = Good_die_Qty FROM Lot_table where Lot_id = (SELECT LOT_ID FROM Shipped_Lot Where ShippedLot_Id = @SHIPPEDLOT_ID )
			--SELECT @GDPW = Parameter_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = (SELECT TOP 1 PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @PROCESSTEP_ID,0 )  WHERE PS_TYPE = 'FAB' ) AND Parameter_Key = 'Gross Die/Wafer'
		--END		
		--SELECT @STD_COST = Parameter_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSTEP_ID AND Parameter_Key = 'Standard Unit Cost'

		SELECT @STD_COST = (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			 END) FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @PROCESSTEP_ID AND YEAR(Fin_Year) = @YEAR

		SET @WAFER_IDS_AS_COMMA = REPLACE( @WAFER_IDS, ';', ',' )
		SET @LOT_NUMBER = SUBSTRING( @LOT_NUMBER, 0, CHARINDEX( '.', @LOT_NUMBER ) )
		INSERT INTO @WAFER_ID_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @WAFER_IDS_AS_COMMA )
		DECLARE @LOT_STRING VARCHAR(50)
		DECLARE @wafer_cursor CURSOR 
		SET @wafer_cursor = CURSOR FOR SELECT WAFER_ID FROM @WAFER_ID_TABLE
		OPEN @wafer_cursor
		FETCH NEXT FROM @wafer_cursor INTO @WAFER_ID
		WHILE @@FETCh_STATUS = 0
		BEGIN
			IF @PS_TYPE = 'SORT'
			BEGIN
				SELECT @GDPW = BIN_1 FROM INFORMATIVE_BINNING WHERE LOT_ID = (SELECT TOP 1 LOT_ID FROM Shipped_Lot Where ShippedLot_Id = @SHIPPEDLOT_ID ) AND Wafer_ID = @WAFER_ID
				IF @GDPW IS NULL OR @GDPW <=0 
				BEGIN
					--SET @LOT_NUMBER = SUBSTRING( @LOT_NUMBER, 0, CHARINDEX( '.', @LOT_NUMBER ) )
					SELECT TOP 1 @GDPW = BIN_1 FROM INFORMATIVE_BINNING WHERE LOT_ID IN ( SELECT LOT_ID FROM LOT_TABLE WHERE Lot_Number Like @LOT_NUMBER+'%' AND Location_ID IN ( SELECT TOP 1 Location_Id FROM Lot_Table LT, Shipped_LOT SL WHERE SL.Lot_ID = LT.Lot_ID AND ShippedLot_Id = @SHIPPEDLOT_ID ) ) AND Wafer_ID = @WAFER_ID
				END
			END	
			ELSE IF @PS_TYPE = 'FAB'
			BEGIN
				SELECT @GDPW = Parameter_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSTEP_ID AND Parameter_Key = 'Gross Die/Wafer'
			END
			ELSE IF @PS_TYPE = 'Sparse Probe'
			BEGIN
				IF  @WAFER_ID  < 10
				BEGIN
					SET @LOT_STRING = @LOT_NUMBER + '-0' + CONVERT(VARCHAR(5), @WAFER_ID )	
				END
				ELSE
				BEGIN
					SET @LOT_STRING = @LOT_NUMBER + '-' + CONVERT(VARCHAR(5), @WAFER_ID )	
				END				
				PRINT @LOT_NUMBER
				SELECT @GDPW = BIN_1 FROM INFORMATIVE_BINNING WHERE LOT_ID = (SELECT TOP 1 LOT_ID FROM LOT_TABLE WHERE LOT_NUMBER LIKE @LOT_STRING+ '%' AND Location_ID in ( SELECT Location_ID from Location WHERE L_Type = 'SORT' ) )  AND Wafer_ID = @WAFER_ID
				IF @GDPW IS NULL
				BEGIN
					SELECT @GDPW = Parameter_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = (SELECT TOP 1 PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @PROCESSTEP_ID,0 )  WHERE PS_TYPE = 'FAB' ) AND Parameter_Key = 'Gross Die/Wafer'
				END
			END
			ELSE IF @PS_TYPE = 'Die Bank'
			BEGIN
				SELECT @GDPW = BIN_1 FROM INFORMATIVE_BINNING WHERE LOT_ID = (SELECT TOP 1 LOT_ID FROM Shipped_Lot Where ShippedLot_Id = @SHIPPEDLOT_ID ) AND Wafer_ID = @WAFER_ID
			END
								
				INSERT INTO #RESULTS_BYWAFER SELECT [Invoice No],[PO Number],[Part Number],[Fab Lot Number],@WAFER_ID,@GDPW,@STD_COST,
													[Ship To],NULL,[From Location] FROM #RESULTS WHERE ShippedLot_Id = @SHIPPEDLOT_ID
			SET @WAFER_ID = NULL
			SET @GDPW  = null
			FETCH NEXT FROM @wafer_cursor INTO @WAFER_ID
		END
		CLOSE @wafer_cursor 
		DEALLOCATE @wafer_cursor

		DELETE FROM @WAFER_ID_TABLE

		SET @SHIPPEDLOT_ID = NULL
		SET @WAFER_IDS = NULL
		SET @PROCESSTEP_ID = NULL
		SET @PS_TYPE = NULL
		SET @GDPW = NULL
		SET @STD_COST = NULL
		SET @WAFER_IDS_AS_COMMA = NULL
		FETCH NEXT FROM @TRANSITCURSOR INTO  @PROCESSTEP_ID,@PS_TYPE, @SHIPPEDLOT_ID, @WAFER_IDS, @LOT_NUMBER
	END
	CLOSE @TRANSITCURSOR
	DEALLOCATE @TRANSITCURSOR
	
	SELECT * FROM #RESULTS_BYWAFER ORDER BY [Part No],[Wafer lot no.], [Wafer ID] 
end



